import pymysql as sql
from flask import jsonify

connect = sql.Connect(
    host='rm-bp131vjnd9b99vmuq2o.mysql.rds.aliyuncs.com',
    port=3306,
    user='lin_432718',
    passwd='Lin817818',
    db='rail_traffic',
    charset='utf8'
)
cursor = connect.cursor()


def hello():
    print('subcompany operation')


def getAllSubcom():
    print('return all subcom')

    sql = "SELECT * FROM subcompany"
    print(sql)
    cursor.execute(sql)
    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有子公司")
        return 0
    else:
        print("查询成功")
        return temp


def newsub(subname, supername):
    print('insert a new subcompany')

    number = getnewnumber()

    sql = "insert into subcompany values( '" + str(number) + "', '" + subname + "',1,'" + supername + "')"
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def getnewnumber():
    sql = "select max(subcom_id) from subcompany"
    cursor.execute(sql)
    data = cursor.fetchone()
    print(data)
    number = int(data[0]) + 1
    print(number)
    str = "%03d" % number
    print(str)

    return str


def editsub(subid, subname, supername):
    print('edit a new subcompany')
    sql = "update subcompany set subcom_name='" + subname + "',subcom_infomation='" + supername + "' where subcom_id=" + subid
    print(sql)
    try:
        cursor.execute(sql)
        connect.commit()
    except:
        return False
    else:
        return True


def searchSubcom(word):
    sql = "SELECT * FROM subcompany WHERE subcom_name LIKE '%" + word + "%'"
    print(sql)
    cursor.execute(sql)

    temp = cursor.fetchall()
    print(temp)
    if temp == ():
        print("没有符合条件的子公司")
        return 0
    else:
        print("搜索成功")
        return temp


def deleteSubcom(word):
    sql = "SELECT subcom_name FROM subcompany WHERE subcom_id =" + str(word)
    cursor.execute(sql)
    name = cursor.fetchall()[0][0]

    sql = "SELECT count(*) from department WHERE attr_com = '" + str(name) +"'"
    print(sql)
    cursor.execute(sql)

    if cursor.fetchall()[0][0] == 0:
        sql = "Delete FROM subcompany WHERE subcom_id =" + str(word)
        print(sql)
        try:
            cursor.execute(sql)
            connect.commit()
        except:
            return False
        else:
            return True
    else:
        return False


